package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.common.SystemException;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.TaskInfoDao;
import com.powerunion.datacollection.model.CollectionTaskVO;
import com.powerunion.datacollection.model.jsonObject.CollectionTaskJSONObject2;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class TaskInfoDaoImpl extends JdbcBase implements TaskInfoDao  {
	
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			CollectionTaskDaoImpl.class);
	
	public boolean delete(String[] codes) throws DaoException{
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "delete from dc_workflow_table where id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		
		for(int i=0;i<codes.length;i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,codes[i]));
			update.addBatchParameter(spSet);
		}
		try{ 
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("删除采集任务失败！");
		}
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "采集任务信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "采集任务信息"
						+ Constants.FAIL);
			}
			return false;
		}
	}

	public String save(CollectionTaskVO ct) throws DaoException{
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
		String sql = "";
		String resultId = "";
		if (ct.getId() == null || ct.getId().equals("")) {//新增
			String id = String.valueOf(this.getSeq("dc_workflow_table"));
			ct.setId(id);
			StringBuffer sqlSave = new StringBuffer();
			sqlSave.append("insert into dc_workflow_table(wf_category_id,wf_name,wf_desc,date_created,crt_user_code,")
				   .append("form_id,proc_id,submit_type,wf_type,periods_time,exactly_time,id) ")
				   .append(" values(?,?,?,?,?,?,?,?,?,?,?,?)");
			sql = sqlSave.toString();
		} else {//修改
			StringBuffer sqlUpdate = new StringBuffer();
			sqlUpdate.append("update dc_workflow_table set wf_category_id=?,wf_name =?,wf_desc=?,date_created=?,crt_user_code=?,")
				   .append("  form_id=?,proc_id=?,submit_type=?,wf_type=?,periods_time=?,exactly_time=? where id =?");
			
			sql = sqlUpdate.toString();
		}
		UpdateOperator update = new UpdateOperator();
		
		update.setSql(sql);
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getCategoryId()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getName()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getDescription()));
		update.addParameter(new SqlParameter(SqlType.DATE,new java.sql.Date(ct.getCreateDate().getTime())));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getCrtUserCode()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getFormId()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getProcId()));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(ct.getSubmitType())));
		
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(ct
				.getType())));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, ct.getPeriodsTime()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, ct.getExactlyTime()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,ct.getId()));
		
		try{
			update.access();
		}catch(Exception e){
			logger.error("保存任务失败,任务id:"+ct.getId()+" 任务名："+ct.getName(), e);
			throw new DaoException("保存任务失败,任务id:"+ct.getId()+" 任务名："+ct.getName());
		}

		int effectCount = update.getEffectCounts();
		if(effectCount > 0) {
			
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "采集任务信息"
						+ Constants.SUCCESS);
			}
			resultId = ct.getId();
			return resultId;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "采集任务信息"
						+ Constants.FAIL);
			}
			return "";
		}
	}
	
	public CollectionTaskVO getBykey(String id) throws DaoException {
		CollectionTaskVO ct = new CollectionTaskVO();
		StringBuffer sql = new StringBuffer();
		sql.append("select d.wf_category_id,d.wf_name,d.wf_desc,d.date_created,d.crt_user_code,")
		   .append("d.form_id,d.proc_id,d.id,b.user_name,d.wf_type ")
		   .append("from dc_workflow_table d left join frameuser b on d.crt_user_code = b.user_code where d.id = ?");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,id));
		try{
			query.access();
			if(query.resultSetAvailable()) {//检查结果集是否可用
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if(rs.rowCount > 0) {
					ct.setId(rs.getFieldValueAsString("id"));
					ct.setCategoryId(rs.getFieldValueAsString("wf_category_id"));
					ct.setName(rs.getFieldValueAsString("wf_name"));
					ct.setDescription(rs.getFieldValueAsString("wf_desc"));
					ct.setCreateDate(rs.getFieldValueAsDate("date_created"));
					ct.setCrtUserCode(rs.getFieldValueAsString("crt_user_code"));
					ct.setFormId(rs.getFieldValueAsString("form_id"));
					ct.setProcId(rs.getFieldValueAsString("proc_id"));
					ct.setCrtUserName(rs.getFieldValueAsString("user_name"));
					ct.setType(rs.getFieldValueAsInteger("wf_type").intValue());
				}
				rs.clearAll();
			}
		}catch(Exception e){
			logger.debug("", e);
			throw new DaoException("getBykey throw error!");
		}

		return ct;
	}

	public String getCollectionTasks(String categoryId,int start,int limit) {
		logger.debug(Constants.INTOMETHOD + "getCollectionTasks");
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select d.wf_category_id, d.wf_name,d.wf_desc,d.date_created,d.crt_user_code,")
		   .append("d.form_id,d.proc_id,d.id,b.user_name,t.item_name,d.wf_type,")
		   .append("f.item_name form_name,d.submit_type,d.periods_time,d.exactly_time from dc_workflow_table d  ")
		   .append(" left join frameuser b on d.crt_user_code = b.user_code left join dc_item_info t on d.proc_id = t.id  ")
		   .append(" left join dc_item_info f on d.form_id = f.id where wf_category_id=? order by date_created desc ");
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			//ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响
			//ResultSet.CONCUR_READ_ONLY,只读
			ps = conn.prepareStatement(sqlStr.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(start+limit);
			ps.setString(1, categoryId);
			rs = ps.executeQuery();
			//将游标移动到要输出的第一条记录
			rs.first();
			rs.relative(start-1);
			
			List pageList = new ArrayList();
			while(rs.next()) {
				CollectionTaskJSONObject2 task = this.getCTJO(rs);
				pageList.add(task);
			}

			// 转化成JSON数组
			JSONArray jsonarr = JSONArray.fromObject(pageList);
			StringBuffer resultStr = new StringBuffer();
			int total = this.totalRecord(conn, categoryId);
			resultStr.append("{total:").append(total).append(",results:")
					.append(jsonarr).append("}");
			logger.debug("getCollectionTasks:" + Constants.SEARCH + "task"
					+ Constants.SUCCESS);
			return resultStr.toString();
			
		} catch (Exception e) {
			logger.error("getCollectionTasks:" + Constants.SEARCH
					+ "task" + Constants.FAIL,e);
			throw new SystemException("search db record error!");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		
	
	}
	
	private int totalRecord(Connection conn,String categoryId){
		int count=0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(id) from dc_workflow_table where wf_category_id = ? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, categoryId);
			rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
			
		}catch(Exception e){
			logger.error("totalRecord:" + Constants.SEARCH
					+ "task totalRecord" + Constants.FAIL);
			logger.error("totalRecord() of throws : ", e);
		}finally{
			DBUtils.close(rs, ps);
		}
		return count;
	}
	
	public final CollectionTaskJSONObject2 getCTJO(ResultSet rs) throws SQLException {
		CollectionTaskJSONObject2 ctjo = new CollectionTaskJSONObject2();
		ctjo.setId(rs.getString("id"));
		ctjo.setCategoryId(rs.getString("wf_category_id"));
		ctjo.setName(rs.getString("wf_name"));
		ctjo.setDescription(rs.getString("wf_desc"));
		String crtDateStr = Format.getInstance().dateToYMD(rs.getDate("date_created"));
		ctjo.setCreateDateStr(crtDateStr);
		ctjo.setCrtUserCode(rs.getString("crt_user_code"));
		ctjo.setFormId(rs.getString("form_id"));
		ctjo.setProcId(rs.getString("proc_id"));
		ctjo.setCrtUserName(rs.getString("user_name"));
		ctjo.setProcName(rs.getString("item_name"));
		ctjo.setFormName(rs.getString("form_name"));
		ctjo.setSubmitType(rs.getString("submit_type"));
		ctjo.setPeriodsTime(rs.getString("periods_time"));
		ctjo.setExactlyTime(rs.getString("exactly_time"));
		ctjo.setType(rs.getInt("wf_type"));

		
		return ctjo;
	}
	
	public int getInstanceNum(String id) throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) num from dc_wf_instance where wf_id = ?");
		
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,id));
		int num = 0;
		try{
			query.access();
			if(query.resultSetAvailable()) {//检查结果集是否可用
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if(rs.rowCount > 0) {
					num = rs.getFieldValueAsInteger("num").intValue();
				}
				rs.clearAll();
			}
		}catch(Exception e){
			logger.debug("", e);
			throw new DaoException("getInstanceNum throw error!");
		}

		return num;
	}

	


}
